package cn.conac.rc.monitor.repository;

import java.util.HashMap;
import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import cn.conac.rc.framework.repository.GenericDao;
import cn.conac.rc.monitor.entity.VLocalMonitorDay;

import com.alibaba.fastjson.JSONObject;


@Repository
public interface VLocalMonitorDayRepository extends GenericDao<VLocalMonitorDay, String> {
	@Query(value = "SELECT substr(tj_date, 0, 7) as tj_date,"
			+ "sum(t.sq_count) as sq_count,"
			+ "sum(t.cksq_count) as cksq_count,"
			+ "sum(t.wssq_count) as wssq_count,"
			+ "sum(t.sl_count) as sl_count,"
			+ "sum(t.bsl_count) as bsl_count,"
			+ "sum(t.bj_count) as bj_count,"
			+ "sum(t.grbj_count) as grbj_count,"
			+ "sum(t.frbj_count) as frbj_count,"
			+ "sum(t.xkbj_count) as xkbj_count,"
			+ "sum(t.ycxbz_count) as ycxbz_count,"
			+ "sum(t.bjcs_count) as bjcs_count,"
			+ "sum(t.bzs_count) as bzs_count, "
			+ "sum(t.tbcxsq_count) as tbcxsq_count, "
			+ "sum(t.tbcxcs_count) as tbcxcs_count, "
			+ "sum(t.wbjcs_count) as wbjcs_count, "
			+ "'#' as dfsxbm,'#' as blbm,'#' as xzqhbm ,sys_guid() as id"
			+ " FROM V_LOCAL_MONITOR_DAY T where t.blbm =:department and t.xzqhbm=:areaCode and t.tj_date BETWEEN :startDate AND :endDate  GROUP BY substr(tj_date, 0, 7) ORDER BY substr(tj_date, 0, 7) ASC NULLS LAST ", nativeQuery = true)
	List<VLocalMonitorDay>  getCountsByMonth(@Param("department") String department,
			@Param("areaCode") String areaCode,
			@Param("startDate") String startDate,
			@Param("endDate") String endDate);

	@Query(value = "SELECT substr(tj_date, 0, 7) as tj_date,"
			+ "sum(t.sq_count) as sq_count,"
			+ "sum(t.cksq_count) as cksq_count,"
			+ "sum(t.wssq_count) as wssq_count,"
			+ "sum(t.sl_count) as sl_count,"
			+ "sum(t.bsl_count) as bsl_count,"
			+ "sum(t.bj_count) as bj_count,"
			+ "sum(t.grbj_count) as grbj_count,"
			+ "sum(t.frbj_count) as frbj_count,"
			+ "sum(t.xkbj_count) as xkbj_count,"
			+ "sum(t.ycxbz_count) as ycxbz_count,"
			+ "sum(t.bjcs_count) as bjcs_count,"
			+ "sum(t.bzs_count) as bzs_count, "
			+ "sum(t.tbcxsq_count) as tbcxsq_count, "
			+ "sum(t.tbcxcs_count) as tbcxcs_count, "
			+ "sum(t.wbjcs_count) as wbjcs_count, "
			+ "'#' as dfsxbm,'#' as blbm,'#' as xzqhbm ,sys_guid() as id"
			+ " FROM V_LOCAL_MONITOR_DAY T where t.xzqhbm=:areaCode and t.tj_date BETWEEN :startDate AND :endDate  GROUP BY substr(tj_date, 0, 7) ORDER BY substr(tj_date, 0, 7) ASC NULLS LAST", nativeQuery = true)
	List<VLocalMonitorDay> getCountsByMonth(@Param("areaCode") String areaCode,
			@Param("startDate") String startDate,
			@Param("endDate") String endDate);

	@Query(value = "SELECT substr(t.tj_date, 0, 4) tj_date,"
			+ "TO_CHAR(TO_DATE(t.tj_date, 'yyyy-MM-dd'), 'Q') blbm,"
			+ "sum(t.sq_count) as sq_count,"
			+ "sum(t.cksq_count) as cksq_count,"
			+ "sum(t.wssq_count) as wssq_count,"
			+ "sum(t.sl_count) as sl_count,"
			+ "sum(t.bsl_count) as bsl_count,"
			+ "sum(t.bj_count) as bj_count,"
			+ "sum(t.grbj_count) as grbj_count,"
			+ "sum(t.frbj_count) as frbj_count,"
			+ "sum(t.xkbj_count) as xkbj_count,"
			+ "sum(t.ycxbz_count) as ycxbz_count,"
			+ "sum(t.bjcs_count) as bjcs_count,"
			+ "sum(t.bzs_count) as bzs_count, "
			+ "sum(t.tbcxsq_count) as tbcxsq_count, "
			+ "sum(t.tbcxcs_count) as tbcxcs_count, "
			+ "sum(t.wbjcs_count) as wbjcs_count, "
			+ "'#' as dfsxbm,'#' as xzqhbm,sys_guid() as id"
			+ " FROM V_LOCAL_MONITOR_DAY T where t.blbm =:department and t.xzqhbm=:areaCode and t.tj_date BETWEEN :startDate AND :endDate  GROUP BY TO_CHAR(TO_DATE(t.tj_date, 'yyyy-MM-dd'), 'Q'),substr(t.tj_date, 0, 4)  ORDER BY substr(t.tj_date, 0, 4),TO_CHAR(TO_DATE(t.tj_date, 'yyyy-MM-dd'), 'Q') ASC NULLS LAST ", nativeQuery = true)
	List<VLocalMonitorDay>  getCountsByQuarter(@Param("department") String department,
			@Param("areaCode") String areaCode,
			@Param("startDate") String startDate,
			@Param("endDate") String endDate);

	@Query(value = "SELECT substr(t.tj_date, 0, 4) tj_date,"
			+ "TO_CHAR(TO_DATE(t.tj_date, 'yyyy-MM-dd'), 'Q') blbm,"
			+ "sum(t.sq_count) as sq_count,"
			+ "sum(t.cksq_count) as cksq_count,"
			+ "sum(t.wssq_count) as wssq_count,"
			+ "sum(t.sl_count) as sl_count,"
			+ "sum(t.bsl_count) as bsl_count,"
			+ "sum(t.bj_count) as bj_count,"
			+ "sum(t.grbj_count) as grbj_count,"
			+ "sum(t.frbj_count) as frbj_count,"
			+ "sum(t.xkbj_count) as xkbj_count,"
			+ "sum(t.ycxbz_count) as ycxbz_count,"
			+ "sum(t.bjcs_count) as bjcs_count,"
			+ "sum(t.bzs_count) as bzs_count, "
			+ "sum(t.tbcxsq_count) as tbcxsq_count, "
			+ "sum(t.tbcxcs_count) as tbcxcs_count, "
			+ "sum(t.wbjcs_count) as wbjcs_count, "
			+ "'#' as dfsxbm,'#' as xzqhbm ,sys_guid() as id"
			+ " FROM V_LOCAL_MONITOR_DAY T where  t.xzqhbm=:areaCode and t.tj_date BETWEEN :startDate AND :endDate  GROUP BY TO_CHAR(TO_DATE(t.tj_date, 'yyyy-MM-dd'), 'Q'),substr(t.tj_date, 0, 4)  ORDER BY substr(t.tj_date, 0, 4),TO_CHAR(TO_DATE(t.tj_date, 'yyyy-MM-dd'), 'Q') ASC NULLS LAST ", nativeQuery = true)
	List<VLocalMonitorDay>  getCountsByQuarter(@Param("areaCode") String areaCode,
			@Param("startDate") String startDate,
			@Param("endDate") String endDate);

	@Query(value = "SELECT substr(tj_date, 0, 4) tj_date,"
			+ "sum(t.sq_count) as sq_count,"
			+ "sum(t.cksq_count) as cksq_count,"
			+ "sum(t.wssq_count) as wssq_count,"
			+ "sum(t.sl_count) as sl_count,"
			+ "sum(t.bsl_count) as bsl_count,"
			+ "sum(t.bj_count) as bj_count,"
			+ "sum(t.grbj_count) as grbj_count,"
			+ "sum(t.frbj_count) as frbj_count,"
			+ "sum(t.xkbj_count) as xkbj_count,"
			+ "sum(t.ycxbz_count) as ycxbz_count,"
			+ "sum(t.bjcs_count) as bjcs_count,"
			+ "sum(t.bzs_count) as bzs_count, "
			+ "sum(t.tbcxsq_count) as tbcxsq_count, "
			+ "sum(t.tbcxcs_count) as tbcxcs_count, "
			+ "sum(t.wbjcs_count) as wbjcs_count, "
			+ "'#' as dfsxbm,'#' as blbm,'#' as xzqhbm ,sys_guid() as id"
			+ " FROM V_LOCAL_MONITOR_DAY T where t.blbm =:department and t.xzqhbm=:areaCode and t.tj_date BETWEEN :startDate AND :endDate  GROUP BY substr(tj_date, 0, 4) ORDER BY substr(tj_date, 0, 4) ASC NULLS LAST ", nativeQuery = true)
	List<VLocalMonitorDay>  getCountsByYear(@Param("department") String department,
			@Param("areaCode") String areaCode,
			@Param("startDate") String startDate,
			@Param("endDate") String endDate);

	@Query(value = "SELECT substr(tj_date, 0, 4) tj_date,"
			+ "sum(t.sq_count) as sq_count,"
			+ "sum(t.cksq_count) as cksq_count,"
			+ "sum(t.wssq_count) as wssq_count,"
			+ "sum(t.sl_count) as sl_count,"
			+ "sum(t.bsl_count) as bsl_count,"
			+ "sum(t.bj_count) as bj_count,"
			+ "sum(t.grbj_count) as grbj_count,"
			+ "sum(t.frbj_count) as frbj_count,"
			+ "sum(t.xkbj_count) as xkbj_count,"
			+ "sum(t.ycxbz_count) as ycxbz_count,"
			+ "sum(t.bjcs_count) as bjcs_count,"
			+ "sum(t.bzs_count) as bzs_count, "
			+ "sum(t.tbcxsq_count) as tbcxsq_count, "
			+ "sum(t.tbcxcs_count) as tbcxcs_count, "
			+ "sum(t.wbjcs_count) as wbjcs_count, "
			+ "'#' as dfsxbm,'#' as blbm,'#' as xzqhbm ,sys_guid() as id"
			+ " FROM V_LOCAL_MONITOR_DAY T where t.xzqhbm=:areaCode and t.tj_date BETWEEN :startDate AND :endDate  GROUP BY substr(tj_date, 0, 4) ORDER BY substr(tj_date, 0, 4) ASC NULLS LAST ", nativeQuery = true)
	List<VLocalMonitorDay>  getCountsByYear(@Param("areaCode") String areaCode,
			@Param("startDate") String startDate,
			@Param("endDate") String endDate);

	@Query(value = "SELECT  t.blbm,"
			+ "sum(t.sq_count) as sq_count,"
			+ "sum(t.cksq_count) as cksq_count,"
			+ "sum(t.wssq_count) as wssq_count,"
			+ "sum(t.sl_count) as sl_count,"
			+ "sum(t.bsl_count) as bsl_count,"
			+ "sum(t.bj_count) as bj_count,"
			+ "sum(t.xkbj_count) as xkbj_count,"
			+ "sum(t.grbj_count) as grbj_count,"
			+ "sum(t.frbj_count) as frbj_count,"
			+ "sum(t.ycxbz_count) as ycxbz_count,"
			+ "sum(t.bjcs_count) as bjcs_count,"
			+ "sum(t.bzs_count) as bzs_count, "
			+ "sum(t.tbcxsq_count) as tbcxsq_count, "
			+ "sum(t.tbcxcs_count) as tbcxcs_count, "
			+ "sum(t.wbjcs_count) as wbjcs_count, "
			+ "'#' as dfsxbm,'#' as tj_date,'#' as xzqhbm ,sys_guid() as id"
			+ " FROM V_LOCAL_MONITOR_DAY T where t.blbm =:department and t.xzqhbm=:areaCode and t.tj_date BETWEEN :startDate AND :endDate  GROUP BY t.blbm ORDER BY t.blbm ASC NULLS LAST ", nativeQuery = true)
	List<VLocalMonitorDay>  getCountsByBlbm(@Param("department") String department,
			@Param("areaCode") String areaCode,
			@Param("startDate") String startDate,
			@Param("endDate") String endDate);

	@Query(value = "SELECT t.blbm,"
			+ "sum(t.sq_count) as sq_count,"
			+ "sum(t.cksq_count) as cksq_count,"
			+ "sum(t.wssq_count) as wssq_count,"
			+ "sum(t.sl_count) as sl_count,"
			+ "sum(t.bsl_count) as bsl_count,"
			+ "sum(t.bj_count) as bj_count,"
			+ "sum(t.xkbj_count) as xkbj_count,"
			+ "sum(t.grbj_count) as grbj_count,"
			+ "sum(t.frbj_count) as frbj_count,"
			+ "sum(t.ycxbz_count) as ycxbz_count,"
			+ "sum(t.bjcs_count) as bjcs_count,"
			+ "sum(t.bzs_count) as bzs_count, "
			+ "sum(t.tbcxsq_count) as tbcxsq_count, "
			+ "sum(t.tbcxcs_count) as tbcxcs_count, "
			+ "sum(t.wbjcs_count) as wbjcs_count, "
			+ "'#' as dfsxbm,'#' as tj_date,'#' as xzqhbm ,sys_guid() as id"
			+ " FROM V_LOCAL_MONITOR_DAY T where t.xzqhbm=:areaCode and t.tj_date BETWEEN :startDate AND :endDate GROUP BY t.blbm ORDER BY t.blbm ASC NULLS LAST ", nativeQuery = true)
	List<VLocalMonitorDay>  getCountsByBlbm(@Param("areaCode") String areaCode,
			@Param("startDate") String startDate,
			@Param("endDate") String endDate);
}

